﻿using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Abp.EntityFrameworkCore;

namespace SportsPlatform.Base
{
    /// <summary>
    /// sql查询列表和分页查询基类
    /// </summary>
    public class SqlRepositorys
    {
        private static readonly object lockobj = new object();

        private static readonly DbContext _context;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="context"></param>
        public SqlRepositorys(DbContext context)
        {
            context = _context;
        }

        /// <summary>
        /// SQL查询List列表
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <param name="parmas">参数条件</param>
        /// <returns></returns>
        public List<Dictionary<String, Object>> SqlQueryData(string Sql, Dictionary<string, dynamic> parmas = null)
        {
            lock (lockobj)
            {
                List<Dictionary<String, object>> list = new List<Dictionary<string, object>>();
                Dictionary<String, object> dic = null;
                using (var cmd = _context.Database.GetDbConnection().CreateCommand())
                {
                    cmd.CommandText = Sql;
                    if (cmd.Connection.State != ConnectionState.Open)
                    {
                        cmd.Connection.Open();
                    }
                    DbParameter[] parameters = null;

                    if (parmas != null && parmas.Count > 0)
                    {
                        parameters = new DbParameter[parmas.Count];
                        int index = -1;
                        foreach (var item in parmas)
                        {
                            index = index + 1;
                            parameters[index] = new SqlParameter(item.Key, (object)item.Value);
                        }
                        cmd.Parameters.Add(parameters);
                    }
                    using (var dataReader = cmd.ExecuteReader())
                    {
                        string colName = "";
                        while (dataReader.Read())
                        {
                            dic = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                colName = dataReader.GetName(i);
                                if (!dic.ContainsKey(colName))
                                {
                                    dic.Add(colName, dataReader[colName]);
                                }
                            }
                            if (dic.Keys.Count > 0)
                            {
                                list.Add(dic);
                            }
                        }
                        cmd.Parameters.Clear();
                        dataReader.Close();
                    }
                }
                return list;
            }
        }

        /// <summary>
        /// Sql分页查询列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="count">返回总条数</param>
        /// <param name="param">条件参数</param>
        /// <returns></returns>
        public List<Dictionary<string, Object>> SqlQueryPageList(string sql, string orderBy, int pageIndex, int pageSize, out int count, Dictionary<string, dynamic> param = null)
        {
            count = 0;
            List<Dictionary<String, Object>> list = new List<Dictionary<string, object>>();
            string strSQL = "select * from (select t.*,row_number() over(order by " + orderBy + ") rownum from (" + sql + ") t ) t0 where rownum>" + ((pageIndex - 1) * pageSize).ToString() + " and rownum<=" + (pageIndex * pageSize).ToString() + "";
            string strCount = "SELECT count(*) as count FROM (" + sql + ") tcount";
            list = SqlQueryData(strSQL, param);
            var data = SqlQueryData(strCount);
            if (data != null && data.Count > 0)
            {
                count = Convert.ToInt32(data.FirstOrDefault()["count"]);
            }

            return list;
        }
    }
}
